package com.ldq.imooc.basics.dataOperation;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author : 李道泉
 * @Description :
 * @Date : 2018/5/5 16:18
 * @Modified by :
 */
public class AccountDao {

    public Account get(Integer id) throws SQLException {

        Account result = new Account();
        Connection conn = DBUtil.getConn();
        StringBuilder sb = new StringBuilder();
        sb.append(" select * from account_info ");
        sb.append(" where id=? ;");

        PreparedStatement ps = conn.prepareStatement(sb.toString());
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();

        while (rs.next()) {

            result.setId(rs.getInt("id"));
            result.setAmount(rs.getDouble("amount"));
            result.setAccount(rs.getString("account"));
            result.setCreateTime(rs.getDate("create_at"));
        }

        return result;
    }

    public void add(Account account) throws Exception {

        Connection coon = DBUtil.getConn();
        String sql = " " +
                "insert into account_info (account,amount) values (?,?);";
        PreparedStatement ps = coon.prepareStatement(sql);

        ps.setString(1, account.getAccount());
        ps.setDouble(2, account.getAmount());
        ps.execute();
    }

    public void delete(Account account) throws Exception {

        Connection coon = DBUtil.getConn();
        String sql = " " +
                " delete from account_info " +
                " where id = ?; ";
        PreparedStatement ps = coon.prepareStatement(sql);

        ps.setInt(1, account.getId());
        ps.execute();
    }

    public void modify(Account account) throws Exception {

        Connection coon = DBUtil.getConn();
        String sql = " " +
                " update account_info set account=?,amount=? " +
                " where id = ? ; ";
        PreparedStatement ps = coon.prepareStatement(sql);

        ps.setString(1, account.getAccount());
        ps.setDouble(2, account.getAmount());
        ps.setInt(3, account.getId());
        ps.execute();
    }

    public List<Account> query(Account account) throws SQLException {
        List<Account> result = new ArrayList<Account>();


        Connection conn = DBUtil.getConn();
        StringBuilder sb = new StringBuilder();
        sb.append(" select * from account_info ");
        sb.append(" where account like ? ;");

        PreparedStatement ps = conn.prepareStatement(sb.toString());
        ps.setString(1, "%" + account.getAccount() + "%");
        ResultSet rs = ps.executeQuery();

        return result;
    }
}
